﻿namespace Hidistro.SqlDal.Comments
{
    using Hidistro.Core;
    using Hidistro.Core.Entities;
    using Hidistro.Entities;
    using Hidistro.Entities.Comments;
    using Hidistro.Membership.Context;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Runtime.InteropServices;
    using System.Text;

    public class ProductReviewDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        private string BuildUserReviewsAndReplysQuery(UserProductReviewAndReplyQuery query)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" SELECT ProductId FROM Hishop_ProductReviews ");
            builder.AppendFormat(" WHERE UserId = {0} ", HiContext.Current.User.UserId);
            builder.AppendFormat(" AND ProductId IN (SELECT ProductId FROM Hishop_Products)", new object[0]);
            builder.Append(" GROUP BY ProductId");
            return builder.ToString();
        }

        public int DeleteProductReview(long reviewId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Hishop_ProductReviews WHERE ReviewId = @ReviewId");
            this.database.AddInParameter(sqlStringCommand, "ReviewId", DbType.Int64, reviewId);
            return this.database.ExecuteNonQuery(sqlStringCommand);
        }

        public ProductReviewInfo GetProductReview(int reviewId)
        {
            ProductReviewInfo info = new ProductReviewInfo();
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_ProductReviews WHERE ReviewId=@ReviewId");
            this.database.AddInParameter(sqlStringCommand, "ReviewId", DbType.Int32, reviewId);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info = ReaderConvert.ReaderToModel<ProductReviewInfo>(reader);
                }
            }
            return info;
        }

        public DbQueryResult GetProductReviews(ProductReviewQuery reviewQuery)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(" ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(reviewQuery.Keywords));
            if (!string.IsNullOrEmpty(reviewQuery.ProductCode))
            {
                builder.AppendFormat(" AND ProductCode LIKE '%{0}%'", DataHelper.CleanSearchString(reviewQuery.ProductCode));
            }
            if (reviewQuery.productId > 0)
            {
                builder.AppendFormat(" AND ProductId = {0}", reviewQuery.productId);
            }
            if (reviewQuery.CategoryId.HasValue)
            {
                builder.AppendFormat(" AND (CategoryId = {0}", reviewQuery.CategoryId.Value);
                builder.AppendFormat(" OR  CategoryId IN (SELECT CategoryId FROM Hishop_Categories WHERE Path LIKE (SELECT Path FROM Hishop_Categories WHERE CategoryId = {0}) + '%'))", reviewQuery.CategoryId.Value);
            }
            return DataHelper.PagingByRownumber(reviewQuery.PageIndex, reviewQuery.PageSize, reviewQuery.SortBy, reviewQuery.SortOrder, reviewQuery.IsCount, "vw_Hishop_ProductReviews", "ProductId", builder.ToString(), "*");
        }

        public DataSet GetProductReviewsNew(ProductReviewQuery reviewQuery)
        {
            StringBuilder builder = new StringBuilder("select top " + reviewQuery.PageSize + " t.* from vw_Hishop_ProductReviews t, (select ProductId, MAX(ReviewId) as maxrid from vw_Hishop_ProductReviews group by ProductId) a");
            builder.Append(" where t.ProductId = a.ProductId and t.ReviewId=a.maxrid");
            builder.AppendFormat(" and ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(reviewQuery.Keywords));
            if (!string.IsNullOrEmpty(reviewQuery.ProductCode))
            {
                builder.AppendFormat(" AND ProductCode LIKE '%{0}%'", DataHelper.CleanSearchString(reviewQuery.ProductCode));
            }
            if (reviewQuery.productId > 0)
            {
                builder.AppendFormat(" AND ProductId = {0}", reviewQuery.productId);
            }
            if (reviewQuery.CategoryId.HasValue)
            {
                builder.AppendFormat(" AND (CategoryId = {0}", reviewQuery.CategoryId.Value);
                builder.AppendFormat(" OR  CategoryId IN (SELECT CategoryId FROM Hishop_Categories WHERE Path LIKE (SELECT Path FROM Hishop_Categories WHERE CategoryId = {0}) + '%'))", reviewQuery.CategoryId.Value);
            }
            DbCommand strCommand = this.database.GetSqlStringCommand(builder.ToString());
            return this.database.ExecuteDataSet(strCommand);
        }

        public int GetProductReviewsCount(int productId)
        {
            StringBuilder builder = new StringBuilder("SELECT count(1) FROM Hishop_ProductReviews WHERE ProductId =" + productId);
            return (int)this.database.ExecuteScalar(CommandType.Text, builder.ToString());
        }

        public DataSet GetUserProductReviewsAndReplys(UserProductReviewAndReplyQuery query, out int total)
        {
            DbCommand storedProcCommand = this.database.GetStoredProcCommand("ac_Member_UserReviewsAndReplys_Get");
            this.database.AddInParameter(storedProcCommand, "PageIndex", DbType.Int32, query.PageIndex);
            this.database.AddInParameter(storedProcCommand, "PageSize", DbType.Int32, query.PageSize);
            this.database.AddInParameter(storedProcCommand, "IsCount", DbType.Boolean, query.IsCount);
            this.database.AddInParameter(storedProcCommand, "UserId", DbType.Int32, HiContext.Current.User.UserId);
            this.database.AddInParameter(storedProcCommand, "sqlPopulate", DbType.String, this.BuildUserReviewsAndReplysQuery(query));
            this.database.AddOutParameter(storedProcCommand, "Total", DbType.Int32, 4);
            DataSet set = this.database.ExecuteDataSet(storedProcCommand);
            set.Relations.Add("PtReviews", set.Tables[0].Columns["ProductId"], set.Tables[1].Columns["ProductId"], false);
            total = (int)this.database.GetParameterValue(storedProcCommand, "Total");
            return set;
        }

        public DataSet GetUserPL()
        {
            DbCommand storedProcCommand = this.database.GetStoredProcCommand("Yb_GetUserPL");
            DataSet set = this.database.ExecuteDataSet(storedProcCommand);
            return set;
        }
        public int GetUserProductReviewsCount()
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT COUNT(ReviewId) AS Count FROM Hishop_ProductReviews WHERE UserId=@UserId");
            this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, HiContext.Current.User.UserId);
            int num = 0;
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (!reader.Read())
                {
                    return num;
                }
                if (DBNull.Value != reader["Count"])
                {
                    num = (int)reader["Count"];
                }
            }
            return num;
        }

        public bool InsertProductReview(ProductReviewInfo review)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("INSERT INTO Hishop_ProductReviews (ProductId, UserId, ReviewText, UserName, UserEmail, ReviewDate, Evaluate) VALUES(@ProductId, @UserId, @ReviewText, @UserName, @UserEmail, @ReviewDate, @Evaluate)");
            this.database.AddInParameter(sqlStringCommand, "ProductId", DbType.Int32, review.ProductId);
            this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, review.UserId);
            this.database.AddInParameter(sqlStringCommand, "ReviewText", DbType.String, review.ReviewText);
            this.database.AddInParameter(sqlStringCommand, "UserName", DbType.String, review.UserName);
            this.database.AddInParameter(sqlStringCommand, "UserEmail", DbType.String, review.UserEmail);
            this.database.AddInParameter(sqlStringCommand, "ReviewDate", DbType.DateTime, DateTime.Now);
            this.database.AddInParameter(sqlStringCommand, "Evaluate", DbType.Int32, review.Evaluate);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public void LoadProductReview(int productId, int userId, out int buyNum, out int reviewNum)
        {
            buyNum = 0;
            reviewNum = 0;
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT COUNT(*) FROM Hishop_ProductReviews WHERE ProductId=@ProductId AND UserId = @UserId SELECT ISNULL(SUM(Quantity), 0) FROM Hishop_OrderItems WHERE ProductId=@ProductId AND OrderId IN" + string.Format(" (SELECT OrderId FROM Hishop_Orders WHERE UserId = @UserId AND OrderStatus = {0})", 5));
            this.database.AddInParameter(sqlStringCommand, "ProductId", DbType.Int32, productId);
            this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, userId);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    reviewNum = (int)reader[0];
                }
                reader.NextResult();
                if (reader.Read())
                {
                    buyNum = (int)reader[0];
                }
            }
        }
    }
}

